<?php

class PembibitanMainNurseryModel{
	private $db; 	// handler
	private $subbagian;
	
	private $validColumn;

	private $data;

	private $result;

	private $sort_by;
	private $sort_order;

	private $search_keyword;
	private $search_field;

	private $limit;
	
	function PembibitanMainNurseryModel($dbObject){
		$this->subbagian 	= 'pembibitan_mainnursery';		
		$this->db 			= $dbObject;
		
		$this->validColumn  = array(
			'tgl_pembibitan',
			'penanggung_jawab', 
			'jml_pekerja',
			'biaya_pekerja',
			'kondisi_awal',
			'catatan',
			
			'nama_kelompok',
			'jml_kelompok',
			'nama_peralatan',
			'jml_peralatan',
			'nama_material',
			'jml_material',
			'satuan_material',
			);
		
		// settting up configuration
		if(($this->start=$this->getGET('start')) == NULL)
			$this->start = 0;

		$this->sort_by 			= $this->getGET('sort_by');
		$this->sort_order 		= $this->getGET('sort_order');
		$this->search_keyword	= $this->getGET('search_keyword');
		$this->search_field		= $this->getGET('search_field');

		if(($this->limit=$this->getGET('limit')) == NULL)
			$this->limit = 50;			
	}
	
	function format($data){
		$result = array();
		foreach($this->validColumn as $v){
			if( isset($data[$v]) )
				$result[$v] = $data[$v];
			else
				$result[$v] = NULL;
		}
		
		return $result;
	}

	private function getGET($param){
		if( isset( $_GET[$param] ) && $_GET[$param] != '' )
			return $_GET[$param];
		else
			return NULL;
	}
	
	public function countAll(){
		$where = $this->prepareWhereClause();
		$q = "SELECT COUNT(*) FROM `caramel_operational`.`pembibitan_mainnursery` $where";
		
		$this->total_record = $this->db->get_var($q);
		
		return $this->total_record;
	}	
	
	private function prepareWhereClause(){
		$where = '';
		if( $this->search_keyword && $this->search_field ){
			switch($this->search_field){
				case 'kelompok':
					$where = "WHERE `id` IN (SELECT `pembibitan_id` FROM `caramel_operational`.`pembibitan_mainnursery_kelompok` WHERE `nama` LIKE '".$this->search_keyword."')";
					break;
				case 'material':
					$where = "WHERE `id` IN (SELECT `pembibitan_id` FROM `caramel_operational`.`pembibitan_mainnursery_material` WHERE `nama` LIKE '".$this->search_keyword."')";
					break;
				case 'peralatan':
					$where = "WHERE `id` IN (SELECT `pembibitan_id` FROM `caramel_operational`.`pembibitan_mainnursery_peralatan` WHERE `nama` LIKE '".$this->search_keyword."')";
					break;
				default:
						$where = "WHERE ".$this->search_field."='".$this->search_keyword."'";
			}
		}
		
		return $where;
	}	
	
	private function getDetailPembibitan($id, $detail){
		$q = '';
		switch($detail){
			case 'material':
				$q = "SELECT `nama`, `jumlah`, `satuan` FROM `caramel_operational`.`pembibitan_mainnursery_material` WHERE `pembibitan_id`='".$id."'";
				break;
			case 'kelompok':
				$q = "SELECT `nama`, `jumlah` FROM `caramel_operational`.`pembibitan_mainnursery_kelompok` WHERE `pembibitan_id`='".$id."'";
				break;
			case 'peralatan':
				$q = "SELECT `nama`, `jumlah` FROM `caramel_operational`.`pembibitan_mainnursery_peralatan` WHERE `pembibitan_id`='".$id."'";
				break;				
		}
		$result = array();
		$r = $this->db->get_results($q, ARRAY_A);
		if( $r )	
			foreach($r as $j) $result[] = $j;
		
		return $result;
	}
	
	public function search(){			
		$where = $this->prepareWhereClause();
		
		// get data
		if( $this->sort_by )
			$q = "
			SELECT 
				`id`, `tgl_pembibitan`, `penanggung_jawab`, `jml_pekerja`,`biaya_pekerja`,`kondisi_awal`,`catatan`
			FROM  `caramel_operational`.`pembibitan_mainnursery` $where
			ORDER BY ".$this->sort_by." ".$this->sort_order." LIMIT ".$this->start.",".$this->limit."";
		else
			$q = "
			SELECT 
				`id`, `tgl_pembibitan`, `penanggung_jawab`, `jml_pekerja`,`biaya_pekerja`,`kondisi_awal`,`catatan`
			FROM  `caramel_operational`.`pembibitan_mainnursery` $where ORDER BY `tgl_pembibitan` DESC
			LIMIT ".$this->start.",".$this->limit."";

		$this->result = $this->db->get_results($q, ARRAY_A);
		
		// detail
		if( count($this->result) > 0 ){
			foreach($this->result as &$r){
				$r['kelompok'] 		  = $this->getDetailPembibitan($r['id'], 'kelompok');
				$r['material'] 		  = $this->getDetailPembibitan($r['id'], 'material');
				$r['peralatan'] 	  = $this->getDetailPembibitan($r['id'], 'peralatan');
			}
		}
		
		return $this->result;
	}

	public function getResult(){
		return $this->result;
	}
	
	private function insertPembibitan(){
		$dataPembibitan = array();
		$pembibitanColumn = array(
			'tgl_pembibitan', 
			'penanggung_jawab', 
			'jml_pekerja',
			'biaya_pekerja',
			'kondisi_awal',
			'catatan'
		);
		
		// copy dari $data ke $dataPembibitan
		foreach($pembibitanColumn as $c){
			$dataPembibitan[$c] = $this->data[$c];
		}
		
		$qHeader = "(`".implode($pembibitanColumn, "`, `")."`)";
		$qBody   = "('".implode($dataPembibitan, "', '")."')";
		
		$query = "INSERT INTO `caramel_operational`.`pembibitan_mainnursery` ".$qHeader." VALUES ".$qBody;
		$this->db->query($query);
		
		$id = $this->db->get_var("SELECT MAX(`Id`) FROM `caramel_operational`.`pembibitan_mainnursery`");
		
		return $id;
	}
	
	private function insertKelompok($id){
		$dataKelompok = array();
		$kelompokColumn = array(
			'nama_kelompok', 
			'jml_kelompok'
		);
		
		// copy dari $data ke $dataKelompok
		foreach($kelompokColumn as $c){
			$dataKelompok[$c] = $this->data[$c];
		}
		
		if( count($dataKelompok['nama_kelompok']) > 0 ){
		
			$qHeader = "(`pembibitan_id`, `nama`, `jumlah`)";			
			$query   = "INSERT INTO `caramel_operational`.`pembibitan_mainnursery_kelompok` ".$qHeader." VALUES ";
			$temp    = '';
			// looping through all material
			for($i=0; $i<count($dataKelompok['nama_kelompok']); $i++){			
				$qBody   = "('".$id."', '".$dataKelompok['nama_kelompok'][$i]."', '".$dataKelompok['jml_kelompok'][$i]."')";
				$temp[]  = $qBody;
			}
			
			$query .= implode($temp, ",").';';
			
			$this->db->query($query);
		}
	}
	
	private function insertMaterial($id){
		$dataMaterial = array();
		$materialColumn = array(
			'nama_material', 
			'jml_material', 
			'satuan_material'
		);
		
		// copy dari $data ke $dataMaterial
		foreach($materialColumn as $c){
			$dataMaterial[$c] = $this->data[$c];
		}
		
		if( count($dataMaterial['nama_material']) > 0 ){
		
			$qHeader = "(`pembibitan_id`, `nama`, `jumlah`, `satuan`)";			
			$query   = "INSERT INTO `caramel_operational`.`pembibitan_mainnursery_material` ".$qHeader." VALUES ";
			$temp    = '';
			// looping through all material
			for($i=0; $i<count($dataMaterial['nama_material']); $i++){			
				$qBody   = "('".$id."', '".$dataMaterial['nama_material'][$i]."', '".$dataMaterial['jml_material'][$i]."', '".$dataMaterial['satuan_material'][$i]."')";
				$temp[]  = $qBody;
			}
			
			$query .= implode($temp, ",").';';
			
			$this->db->query($query);
		}
	}
	
	private function insertPeralatan($id){
		$dataPeralatan = array();
		$peralatanColumn = array(
			'nama_peralatan', 
			'jml_peralatan'
		);
		
		// copy dari $data ke $dataPeralatan
		foreach($peralatanColumn as $c){
			$dataPeralatan[$c] = $this->data[$c];
		}
		
		if( count($dataPeralatan['nama_peralatan']) > 0 ){
		
			$qHeader = "(`pembibitan_id`, `nama`, `jumlah`)";			
			$query   = "INSERT INTO `caramel_operational`.`pembibitan_mainnursery_peralatan` ".$qHeader." VALUES ";
			$temp    = '';
			// looping through all material
			for($i=0; $i<count($dataPeralatan['nama_peralatan']); $i++){			
				$qBody   = "('".$id."', '".$dataPeralatan['nama_peralatan'][$i]."', '".$dataPeralatan['jml_peralatan'][$i]."')";
				$temp[]  = $qBody;
			}
			
			$query .= implode($temp, ",").';';
			
			$this->db->query($query);
		}
	}
	
	function put($data){		
		$this->data	= $this->format($data);// ambil  field2 yang dibutuhkan saja	
		
		if( $this->isExist() == FALSE ){
			// insert ke pembibitan mainnursery
			$id = $this->insertPembibitan();
			// kelompok
			$this->insertKelompok($id);		
			// material
			$this->insertMaterial($id);		
			// peralatan
			$this->insertPeralatan($id);
		}
		else{
			return INSERT_FAIL_DUPLICATE;
		}		
	}	
	
	function isExist(){
		// single query
		// melihat duplikasi dari field yang unique dari table ybs
		// cek primary key
		
		$whereClause = "`tgl_pembibitan`='".$this->data['tgl_pembibitan']."' AND `penanggung_jawab`='".$this->data['penanggung_jawab']."'";
		$query 		 = "SELECT COUNT(*) FROM `caramel_operational`.`pembibitan_mainnursery` WHERE ".$whereClause;
		$count 		 = $this->db->get_var($query);
		
		if( $count > 0 )
			return TRUE;
			
		return FALSE; // tidak ada data yang dicari... no duplicate		
	}
    
    public function delete($id){
        // pembibitan
        $this->deletePembibitan($id);
        // kelompok
        $this->deleteKelompok($id);
        // peralatan
        $this->deletePeralatan($id);
        // material
        $this->deleteMaterial($id);
    }
    
    
    private function getKelompokById($id){
        $data = array();
        $q = "
            SELECT `nama`, `jumlah` 
            FROM `caramel_operational`.`pembibitan_mainnursery_kelompok`
            WHERE `pembibitan_id`='$id'
            ";
        $data = $this->db->get_results($q, ARRAY_A);
        
        if( $data != NULL && count($data) > 0 ){            
            return $data;
        }
        else{
            return NULL;
        }   
    }
    
    private function getMaterialById($id){
        $data = array();
        $q = "
            SELECT `nama`, TRUNCATE(`jumlah`,3) as `jumlah`, `satuan`
            FROM `caramel_operational`.`pembibitan_mainnursery_material`
            WHERE `pembibitan_id`='$id'
            ";
        $data = $this->db->get_results($q, ARRAY_A);
        
        if( $data != NULL && count($data) > 0 ){            
            return $data;
        }
        else{
            return NULL;
        }
    }
    
    private function getPeralatanById($id){
        $data = array();
        $q = "
            SELECT `nama`, `jumlah` 
            FROM `caramel_operational`.`pembibitan_mainnursery_peralatan`
            WHERE `pembibitan_id`='$id'
            ";
        $data = $this->db->get_results($q, ARRAY_A);
        
        if( $data != NULL && count($data) > 0 ){            
            return $data;
        }
        else{
            return NULL;
        }   
    }
    
    public function getById($id){
        $q = "SELECT * FROM `caramel_operational`.`pembibitan_mainnursery` WHERE `id`='$id'";
        $r = $this->db->get_results($q, ARRAY_A);
        
        if( is_array($r) && count($r) > 0 ){
            // main data..
            $data = array_pop($r);
            
            // kelompok
            $data['kelompok']  = $this->getKelompokById($id);
            // material
            $data['material']  = $this->getMaterialById($id);
            // peralatan
            $data['peralatan'] = $this->getPeralatanById($id);
            
            return $data;
        }
        else{
            return NULL;
        }
    }
    
    private function deletePembibitan($id){
        $q = "
        DELETE FROM `caramel_operational`.`pembibitan_mainnursery` 
        WHERE `id`='$id'";
        $this->db->query($q);
    }
    
    private function deleteKelompok($pembibitan_id){
        $q = "
            DELETE FROM `caramel_operational`.`pembibitan_mainnursery_kelompok`
            WHERE `pembibitan_id`='$pembibitan_id'
            ";
        $this->db->query($q);
    }
    
    private function deletePeralatan($pembibitan_id){
        $q = "
            DELETE FROM `caramel_operational`.`pembibitan_mainnursery_peralatan`
            WHERE `pembibitan_id`='$pembibitan_id'
            ";
        $this->db->query($q);
    }
    
    private function deleteMaterial($pembibitan_id){
        $q = "
            DELETE FROM `caramel_operational`.`pembibitan_mainnursery_material`
            WHERE `pembibitan_id`='$pembibitan_id'
            ";
        $this->db->query($q);
    }    
    
    public function editById($data, $id){
        $valid = $this->getById($id);
        if( $valid == NULL ){
            return EDIT_ID_INVALID;
        }
        
        // populate data
        $this->data	= $this->format($data);
        
        $q = "
            UPDATE `caramel_operational`.`pembibitan_mainnursery`
            SET 
                `tgl_pembibitan`='".$this->data['tgl_pembibitan']."',
                `penanggung_jawab`='".$this->data['penanggung_jawab']."',
                `jml_pekerja`='".$this->data['jml_pekerja']."',
                `biaya_pekerja`='".$this->data['biaya_pekerja']."',
                `kondisi_awal`='".$this->data['kondisi_awal']."',
                `catatan`='".$this->data['catatan']."'
            WHERE `id`='$id'
            ";
        if( $this->db->query($q) ){
            // hapus (refresh)
            // kelompok
            $this->deleteKelompok($id);            
            // material
            $this->deleteMaterial($id);
            // peralatan
            $this->deletePeralatan($id);
            
            // kemudian insert semuanya
            // kelompok
			$this->insertKelompok($id);		
			// material
			$this->insertMaterial($id);		
			// peralatan
			$this->insertPeralatan($id);
            
            return EDIT_OK;
        }
        else{
            return EDIT_FAIL;
        }        
    }
}
?>